import json
import random
from hashlib import md5
from datetime import timedelta
import pymysql
from flask import Flask, render_template, request, jsonify, session
from MySQL.base import MySQLHelper

app = Flask(__name__)
app.config["DEBUG"] = True
app.config["SEND_FILE_MAX_AGE_DEFAULT"] = timedelta(seconds=1)  # 缓存时间为1秒
app.config['SECRET_KEY'] = 'you never guess'  # session使用设置

binfo = ["id", "book_id", "book_name", "author", "book_des", "book_price", "book_count", "chap_1", "book_clas", "img"]
SQL_info = "host='127.0.0.1', port=3306, user='root',passwd='lales1999', db='book_shop', charset='utf8'"
# d_num = {1: '玄幻奇幻', 2: '武侠仙侠', 3: '都市生活', 4: '历史军事', 5: '游戏竞技', 6: '科幻未来',
#          7: '恐怖悬疑', 8: '二次元', 9: '经典网文', 10: '古代言情', 11: '现代言情', 12: '幻想奇缘',
#          13: '青春校园', 14: '网络情缘', 15: '科幻空间', 16: '鬼怪灵异', 17: 'N次元', 18: '言情美文'}
# d_let = {'A': '玄幻奇幻', 'B': '武侠仙侠', 'C': '都市生活', 'D': '历史军事', 'E': '游戏竞技', 'F': '科幻未来',
#          'G': '恐怖悬疑', 'H': '二次元', 'I': '经典网文', 'J': '古代言情', 'K': '现代言情', 'L': '幻想奇缘',
#          'M': '青春校园', 'N': '网络情缘', 'O': '科幻空间', 'P': '鬼怪灵异', 'Q': 'N次元', 'R': '言情美文'}


def Select_info(sql):
    conn = pymysql.Connect(host='localhost', port=3306, user='root', passwd='lales1999', db='book_shop', charset='utf8')
    cur = conn.cursor()
    try:
        cur.execute(sql)
    except:
        print("查询出错！！！")
        return 0
    sql_res = cur.fetchall()
    binfo = ["id", "book_id", "book_name", "author", "book_des", "book_price", "book_count", "chap_1", "book_clas",
             "img"]
    res = list()
    for item in sql_res:
        v = list(item)
        d = dict(zip(binfo, v))
        res.append(d)
    conn.commit()
    cur.close()
    conn.close()
    return res


def Sel_SQL(sql):
    conn = pymysql.Connect(host='localhost', port=3306, user='root', passwd='lales1999', db='book_shop', charset='utf8')
    cur = conn.cursor()
    try:
        cur.execute(sql)
    except:
        print("Oper_MYSQL出错", sql)
        return None
    res = cur.fetchall()
    conn.commit()
    cur.close()
    conn.close()
    return res


def Ins_SQL(sql):
    conn = pymysql.Connect(host='localhost', port=3306, user='root', passwd='lales1999', db='book_shop', charset='utf8')
    cur = conn.cursor()
    flag = 0
    try:
        cur.execute(sql)
        flag = 1
    except:
        print("Oper_MYSQL出错", sql)
    conn.commit()
    cur.close()
    conn.close()
    return flag


@app.route('/welcomeadmin')
def welcomeadmin():
    return render_template("welcomeadmin.html")


@app.route("/artList")
def artList():
    return render_template("artList.html")


@app.route("/findArtList")
def findArtList():
    limit = request.args.get("limit")
    page = request.args.get("page")
    conn = MySQLHelper();
    con = pymysql.Connect(host='127.0.0.1', port=3306, user='root', passwd='lales1999', db='book_shop', charset='utf8')
    cur = con.cursor()
    sql = "select * from book_info limit %s,%s"
    rs = conn.executeForSelectAll(sql, [(int(page) - 1) * int(limit), int(limit)])
    listObject = [];
    for item in rs:
        clas = tuple(item)[8]
        sql = "select cla_name from bookclass where cla_id = '%s'" % (clas)
        cur.execute(sql)
        dictObject = {}
        dictObject["id"] = tuple(item)[0]
        dictObject["book_id"] = tuple(item)[1]
        dictObject["book_name"] = tuple(item)[2]
        dictObject["author"] = tuple(item)[3]
        dictObject["book_price"] = tuple(item)[5]
        dictObject["book_count"] = tuple(item)[6]
        dictObject["leibie"] = cur.fetchone()[0]
        listObject.append(dictObject)
        myDictData = {}
        myDictData["code"] = 0
        myDictData["data"] = listObject
        # myDictData["count"] = count
    con.commit()
    cur.close()
    con.close()
    return json.dumps(myDictData, ensure_ascii=False);


@app.route("/classList")
def classList():
    return render_template("classList.html")


@app.route("/findClassList")
def findClassList():
    limit = request.args.get("limit")
    page = request.args.get("page")
    conn = MySQLHelper();
    sql = "select * from bookclass limit %s,%s"
    rs = conn.executeForSelectAll(sql, [(int(page) - 1) * int(limit), int(limit)])
    sql1 = "select count(*) as `count` from bookclass"
    rs1 = conn.executeForSelectAll(sql1, ())
    sql3 = "select sum(book_count) as `count1` from book_info where book_clas=%s"
    listsum = [];
    m = 1
    for m in range(1, 12):
        res1 = conn.executeForSelectAll(sql3, (str(m)))
        # m=m+1;
        listsum.append(res1[0][0])
    print("-------")
    print(listsum)
    print("-------")
    count = 0
    for i in rs1:
        count = tuple(i)[0]
    print("count=", count)
    listObject = [];
    mm = 0;
    for item in rs:
        dictObject = {}
        # print(item)
        dictObject["clid"] = tuple(item)[0]
        dictObject["cla-name"] = tuple(item)[1]
        dictObject['cla-nums'] = listsum[mm]
        mm = mm + 1;
        listObject.append(dictObject)
        myDictData = {}
        myDictData["code"] = 0
        myDictData["data"] = listObject
        myDictData["count"] = count
    # print(json.dumps(myDictData, ensure_ascii=False))
    return json.dumps(myDictData, ensure_ascii=False)


@app.route("/getArtYearDataForLine")
def getArtYearDataForLine():
    conn = MySQLHelper()
    sqla = "select book_price from book_info"
    rs0 = conn.executeForSelectAll(sqla, ())
    re = []
    for i in range(len(rs0)):
        re.append(rs0[i][0])
    # print(re)
    dic = {}
    dic["re"] = re
    # print(dic)
    return json.dumps(dic, ensure_ascii=False)


@app.route("/manager")
def manager():
    return render_template("admin.html")


@app.route("/artSys")
def artSys():
    return render_template("artSys.html")


@app.route("/buylist")
def buylist():
    print(f"查询购物车列表sessin['login_code']:{session['login_code']}\tsessin['email']:{session['email']}")
    if session["login_code"] and session["email"]:
        email = session["email"]
        sql_e = "select book_id from book_buy where email = '%s'" % email
        sel_res = Sel_SQL(sql_e)  # 从数据库查询当前email的buy_book_id
        res = list()
        for item in sel_res:
            id = item[0]
            sql_id = "select * from book_info where book_id = '%s'" % id
            id_res = Select_info(sql_id)
            res.append(id_res[0])
        return render_template("ShowBuyBookList.html", res=res)
    else:
        return render_template("regis_login.html", res="，未登录，请登录")


@app.route("/addbuy")
def addbuy():
    print(f"添加购物车列表sessin['login_code']:{session['login_code']}\tsessin['email']:{session['email']}")
    if session["login_code"] and session["email"]:
        bid = request.args.get("bid")
        uemail = session["email"]
        sql_ins = "insert into book_buy(email, book_id) values ('%s', '%s')" % (uemail, bid)
        res = Ins_SQL(sql_ins)
        return res
    else:
        return render_template("regis_login.html", res="，未登录，请登录")


@app.route('/register', methods=["post", "get"])  # 注册调用
def register():
    email = request.form.get("uname_register")  # 此处需要前端传值
    pwd = md5(request.form.get("upwd_register").encode("utf8")).hexdigest()
    name = request.form.get("nickname")
    sql_exist = "SELECT email FROM user where email = '%s' limit 1" % email  # 检查邮箱是否注册过
    res_exist = Sel_SQL(sql_exist)
    if len(res_exist):
        print("exist!", res_exist, len(res_exist))
        return "2"
    sql_ins = "insert into user(email, password, name, stats) values ('%s', '%s', '%s', '%s')" \
              % (email, pwd, name, 0)
    try:
        Ins_SQL(sql_ins)
        print(f"{email}用户{name}成功创建")
        return "1"
    except pymysql.ProgrammingError as e:
        print(f"{email}插入数据库出错！", e)
    return "0"


@app.route("/loginANDregister", methods=["get", "post"])
def loginANDregister():
    return render_template("regis_login.html")


@app.route('/login', methods=["post", "get"])  # 登陆调用
def login():
    email = request.form.get("uname_login", "")  # 此处需要前端传值
    pwd = md5(request.form.get("upwd_login", "").encode("utf8"))
    conn = pymysql.Connect(host='127.0.0.1', port=3306, user='root', passwd='lales1999', db='book_shop', charset='utf8')
    cur = conn.cursor()
    sql_sele = "SELECT password, stats, name from user where email = '%s'" % (email)
    cur.execute(sql_sele)
    res = cur.fetchone()
    result = {"stats": "0"}
    if res is None:
        # return render_template("login.html", res={"message": "请检查密码是否正确！"})
        result = {"stats": "2"}
    else:
        pwd_sql, stats, name = res[0], res[1], res[2]
        if pwd.hexdigest() == pwd_sql:
            session["login_code"] = True
            session["email"] = email
            print(f"sessin['login_code']:{session['login_code']}\tsessin['email']:{session['email']}")
            if stats == 1:
                result = {"stats": "11", "name": name}
            elif stats == 0:
                result = {"stats": "10", "name": name}
    print(result)
    return jsonify(result)


@app.route('/main')
def main():
    clas_num = random.randint(1, 18)
    print(f"随机推荐类别是{clas_num}")
    sql = "select * from book_info where book_clas = '%s' limit 4" % clas_num
    res = Select_info(sql)
    if not res:
        return render_template("main.html", error="关键字错误或其他未知错误！")
    return render_template("main.html", res=res)


@app.route("/bookdetails", methods=["get", "post"])
def bookdetails():
    bid= request.args.get("bid")
    print("前端传回参数bid是：", bid)
    sql = "select * from book_info where book_id = '%s' limit 1" % bid
    res = Select_info(sql)[0]
    if not res:
        return render_template("main.html")
    return render_template("BookDetails.html", res=res)


@app.route("/index", methods=["post", "get"])
def index():
    if session["login_code"]:
        username = request.args.get("username")
        return render_template("index.html", username=username)
    return render_template("index.html", username=None)


@app.route('/', methods=["post", "get"])
def Book_shop():  # 测试页面，测试将数据拿到并且返回html文件
    session["login_code"] = False
    session["email"] = None
    print(f"登出之后或者未登录：sessin['login_code']:{session['login_code']}\tsessin['email']:{session['email']}")
    return render_template("index.html", username=None)


if __name__ == '__main__':
    app.run(debug=True)
